#!/usr/bin/env python3
"""
Summary提取脚本 - 从数据库中提取需要翻译的summary内容
生成结构化的文本文件便于人工或AI翻译
"""

import sqlite3
import os
import logging
from typing import List, Tuple
import re

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

class SummaryExtractor:
    """Summary提取器"""

    def __init__(self, db_path: str):
        self.db_path = db_path
        self.conn = None

    def connect(self):
        """连接数据库"""
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.conn.execute("PRAGMA foreign_keys = ON")
            logger.info(f"成功连接数据库: {self.db_path}")
            return True
        except Exception as e:
            logger.error(f"连接数据库失败: {e}")
            return False

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            logger.info("数据库连接已关闭")

    def clean_text(self, text: str) -> str:
        """清理文本，移除多余的空白字符"""
        if not text:
            return ""

        # 移除换行符并合并空白字符
        text = re.sub(r'\s+', ' ', text.strip())

        # 移除首尾空白
        text = text.strip()

        return text

    def extract_metrics_summaries(self) -> List[Tuple[int, str]]:
        """提取Metrics表的summary内容"""
        logger.info("提取Metrics表的summary内容...")

        try:
            cursor = self.conn.cursor()
            cursor.execute("""
                SELECT id, summary FROM metrics
                WHERE summary IS NOT NULL AND summary != ''
                ORDER BY id
            """)

            rows = cursor.fetchall()
            summaries = [(row[0], self.clean_text(row[1])) for row in rows]

            logger.info(f"提取到 {len(summaries)} 条Metrics summary记录")
            return summaries

        except Exception as e:
            logger.error(f"提取Metrics summary失败: {e}")
            return []

    def extract_variants_summaries(self) -> List[Tuple[int, str]]:
        """提取Variants表的summary内容"""
        logger.info("提取Variants表的summary内容...")

        try:
            cursor = self.conn.cursor()
            cursor.execute("""
                SELECT id, variant_summary FROM variants
                WHERE variant_summary IS NOT NULL AND variant_summary != ''
                ORDER BY id
            """)

            rows = cursor.fetchall()
            summaries = [(row[0], self.clean_text(row[1])) for row in rows]

            logger.info(f"提取到 {len(summaries)} 条Variants summary记录")
            return summaries

        except Exception as e:
            logger.error(f"提取Variants summary失败: {e}")
            return []

    def extract_enum_values_summaries(self) -> List[Tuple[int, str]]:
        """提取Enum_values表的summary内容"""
        logger.info("提取Enum_values表的summary内容...")

        try:
            cursor = self.conn.cursor()
            cursor.execute("""
                SELECT id, summary FROM enum_values
                WHERE summary IS NOT NULL AND summary != ''
                ORDER BY id
            """)

            rows = cursor.fetchall()
            summaries = [(row[0], self.clean_text(row[1])) for row in rows]

            logger.info(f"提取到 {len(summaries)} 条Enum_values summary记录")
            return summaries

        except Exception as e:
            logger.error(f"提取Enum_values summary失败: {e}")
            return []

    def save_to_file(self, summaries: List[Tuple[int, str]], filename: str):
        """保存summary到文件"""
        try:
            with open(filename, 'w', encoding='utf-8') as f:
                # 写入文件头部说明
                f.write(f"# Summary提取文件: {filename}\n")
                f.write(f"# 总记录数: {len(summaries)}\n")
                f.write("# 格式: ID|Original Summary\n")
                f.write("# 请将翻译内容保存为对应的_zh.txt文件\n")
                f.write("# 格式: ID|Translated Summary\n")
                f.write("\n")

                # 写入数据
                for record_id, summary in summaries:
                    # 转义管道符号和换行符
                    escaped_summary = summary.replace('|', '\\|').replace('\n', '\\n')
                    f.write(f"{record_id}|{escaped_summary}\n")

            logger.info(f"已保存 {len(summaries)} 条记录到 {filename}")

        except Exception as e:
            logger.error(f"保存文件失败: {e}")

    def generate_translation_guide(self):
        """生成翻译指导文件"""
        guide_content = """# Summary翻译指导

## 翻译原则
1. **准确性**: 确保技术术语翻译准确
2. **一致性**: 相同概念保持翻译一致
3. **可读性**: 译文要符合中文表达习惯
4. **完整性**: 不遗漏任何技术细节

## 技术术语参考
- Accessibility -> 无障碍访问
- Engine -> 引擎
- Callback -> 回调
- Component -> 组件
- State -> 状态
- Event -> 事件
- Handler -> 处理器
- Thread -> 线程
- Process -> 进程
- Cache -> 缓存
- Request -> 请求
- Response -> 响应
- Configuration -> 配置

## 翻译步骤
1. 打开对应的原始文件（如metrics_summaries.txt）
2. 创建对应的翻译文件（如metrics_summaries_zh.txt）
3. 逐行翻译，保持ID不变
4. 使用格式: ID|Translated Summary
5. 确保每行都有对应的翻译

## 质量检查
- 检查是否有遗漏的翻译
- 确认技术术语一致性
- 验证中文表达流畅性
- 检查ID匹配是否正确

## 注意事项
- 保持专业术语的准确性
- 避免直译，注重语境理解
- 对于不确定的术语，可以保持英文或添加注释
- 确保翻译后的长度适中，便于界面显示
"""

        try:
            with open('translation_guide.md', 'w', encoding='utf-8') as f:
                f.write(guide_content)
            logger.info("已生成翻译指导文件: translation_guide.md")
        except Exception as e:
            logger.error(f"生成翻译指导文件失败: {e}")

    def extract_all(self):
        """提取所有summary内容"""
        logger.info("开始提取所有summary内容...")

        # 提取各表的summary
        metrics_summaries = self.extract_metrics_summaries()
        variants_summaries = self.extract_variants_summaries()
        enum_summaries = self.extract_enum_values_summaries()

        # 保存到文件
        if metrics_summaries:
            self.save_to_file(metrics_summaries, 'metrics_summaries.txt')

        if variants_summaries:
            self.save_to_file(variants_summaries, 'variants_summaries.txt')

        if enum_summaries:
            self.save_to_file(enum_summaries, 'enum_summaries.txt')

        # 生成翻译指导
        self.generate_translation_guide()

        # 生成统计报告
        total_summaries = len(metrics_summaries) + len(variants_summaries) + len(enum_summaries)
        logger.info(f"Summary提取完成！")
        logger.info(f"- Metrics表: {len(metrics_summaries)} 条")
        logger.info(f"- Variants表: {len(variants_summaries)} 条")
        logger.info(f"- Enum_values表: {len(enum_summaries)} 条")
        logger.info(f"- 总计: {total_summaries} 条")

def main():
    """主函数"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 从 extractor/translate -> extractor -> project root -> backend/data
    project_root = os.path.dirname(os.path.dirname(current_dir))
    db_path = os.path.join(project_root, 'backend', 'data', 'uma_insight.db')

    if not os.path.exists(db_path):
        logger.error(f"数据库文件不存在: {db_path}")
        return

    extractor = SummaryExtractor(db_path)

    if not extractor.connect():
        return

    try:
        extractor.extract_all()
        logger.info("所有summary内容已成功提取到文本文件")
        logger.info("请根据translation_guide.md进行翻译工作")

    except KeyboardInterrupt:
        logger.info("用户中断提取过程")
    except Exception as e:
        logger.error(f"提取过程出错: {e}")
    finally:
        extractor.close()

if __name__ == "__main__":
    main()